package dao;

import db.DbClose;
import db.DbConn;
import entity.Goods;
import tools.ScannerChoice;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * 数据库goods表的操作
 *
 * @author hy
 * @data 2017/10/18
 */
public class GoodsDao {

    Connection conn = null;
    PreparedStatement psmt = null;
    ResultSet rs = null;


    /**
     * 添加商品到数据库的goods表
     *
     * @param goods
     * @return boolean
     */
    public boolean addGoods(Goods goods) {

        boolean bool = false;
        conn = DbConn.getconn();
        String sql = "INSERT INTO GOODS(GNAME,GPRICE,GNUM) VALUES(?,?,?)";

        try {
            psmt = conn.prepareStatement(sql);
            psmt.setString(1, goods.getGname());
            psmt.setDouble(2, goods.getGprice());
            psmt.setInt(3, goods.getGnum());

            int rs = psmt.executeUpdate();
            if (rs > 0) {
                bool = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbClose.addClose(psmt, conn);
        }
        return bool;
    }


    /**
     * 更改商品到数据库的goods表
     *
     * @param key
     * @param goods
     * @return
     */
    public boolean updateGoods(int key, Goods goods) {
        boolean bool = false;
        conn = DbConn.getconn();
        switch (key) {
            case 1:
                String sqlName = "UPDATE GOODS SET GNAME = ? where GID=?";
                try {
                    psmt = conn.prepareStatement(sqlName);
                    psmt.setString(1, goods.getGname());
                    psmt.setInt(2, goods.getGid());
                    int rs = psmt.executeUpdate();
                    if (rs > 0) {
                        bool = true;
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    DbClose.addClose(psmt, conn);
                }

                break;
            case 2:
                String sqlPrice = "update GOODS SET GNUM =? where GID=?";
                try {
                    psmt = conn.prepareStatement(sqlPrice);
                    psmt.setInt(1, goods.getGnum());
                    psmt.setInt(2, goods.getGid());
                    int rs = psmt.executeUpdate();
                    if (rs > 0) {
                        bool = true;
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    DbClose.addClose(psmt, conn);
                }
                break;
            case 3:
                String sqlNum = "UPDATE GOODS SET GNUM=? WHERE GID=?";
                try {
                    psmt = conn.prepareStatement(sqlNum);
                    psmt.setInt(1, goods.getGnum());
                    psmt.setInt(2, goods.getGid());
                    int rs = psmt.executeUpdate();
                    if (rs > 0) {
                        bool = true;
                    }

                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    DbClose.addClose(psmt, conn);
                }
                break;
            default:
                break;
        }
        return bool;
    }

    /**
     * 删除商品信息
     *
     * @param gId
     * @return
     */
    public boolean deleteGoods(int gId) {
        boolean bool = false;
        conn = DbConn.getconn();
        String sql = "DELETE FROM GOODS WHERE GID = ?";
        try {
            psmt = conn.prepareStatement(sql);
            psmt.setInt(1, gId);
            int rs = psmt.executeUpdate();
            if (rs > 0) {
                bool = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbClose.addClose(psmt, conn);
        }
        return bool;
    }

    /**
     * 查询商品信息
     *
     * @param key
     * @return
     */
    public ArrayList<Goods> queryGoods(int key) {
        ArrayList<Goods> goodsArrayList = new ArrayList<Goods>();
        conn = DbConn.getconn();

        switch (key) {
            case 1:
                //按数量升序排列
                String sqlGnum = "SELECT * FROM GOODS ORDER BY GNUM ASC";
                try {
                    psmt = conn.prepareStatement(sqlGnum);
                    rs = psmt.executeQuery();
                    while (rs.next()) {
                        int gid = rs.getInt("gId");
                        String gname = rs.getString(2);
                        double gprice = rs.getDouble(3);
                        int gnum = rs.getInt(4);

                        Goods goods = new Goods(gid, gname, gprice, gnum);
                        goodsArrayList.add(goods);
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    DbClose.queryClose(psmt, rs, conn);
                }
                break;
            case 2:
                //按价格升序排列
                String sqlGprice = "SELECT * FROM GOODS ORDER BY GPRICE ASC";
                try {
                    psmt = conn.prepareStatement(sqlGprice);
                    rs = psmt.executeQuery();
                    while (rs.next()) {
                        int gid = rs.getInt("gid");
                        String gname = rs.getString(2);
                        double gprice = rs.getDouble(3);
                        int gnum = rs.getInt(4);

                        Goods goods = new Goods(gid, gname, gprice, gnum);
                        goodsArrayList.add(goods);
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    DbClose.queryClose(psmt, rs, conn);
                }
                break;
            case 3:
                String nameGet = ScannerChoice.ScannerInfoString();
                String sqlGname = "SELECT * FROM GOODS WHERE GNAME LIKE '&'||?||'&'";

                try {
                    psmt = conn.prepareStatement(sqlGname);
                    psmt.setString(1, nameGet);
                    rs = psmt.executeQuery();
                    while (rs.next()) {
                        int gid = rs.getInt("gid");
                        String gname = rs.getString(2);
                        double gprice = rs.getDouble(3);
                        int gnum = rs.getInt(4);

                        Goods goods = new Goods(gid, gname, gprice, gnum);
                        goodsArrayList.add(goods);
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    DbClose.queryClose(psmt, rs, conn);
                }
                break;
            default:
                break;
        }
        return goodsArrayList;
    }

    /**
     * 显示所有商品信息
     *
     * @return
     */
    public ArrayList<Goods> displayGoods() {
        ArrayList<Goods> goodsArrayList = new ArrayList<Goods>();
        conn = DbConn.getconn();
        String sql = "select * from goods";

        try {
            psmt = conn.prepareStatement(sql);
            rs = psmt.executeQuery();
            while (rs.next()) {
                int gid = rs.getInt(1);
                String gname = rs.getString(2);
                double gprice = rs.getDouble("gprice");
                int gnum = rs.getInt(4);
                Goods goods = new Goods(gid, gname, gprice, gnum);
                goodsArrayList.add(goods);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbClose.queryClose(psmt, rs, conn);
        }
        return goodsArrayList;
    }


}
